﻿/// -----------------------------------------------------------------
/// KeywordManipulator.cs: provides methods for extracting, 
/// updating and deleting Keywords in the Database.
/// License: see http://mve.codeplex.com/license; All Rights follows the MS-PL
/// Current owner: shiniwa
/// The project decription: please refer to http://codeplex.com/mve/
/// -----------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

namespace MVEContentSvc.KeywordOperations
{
    /*
     * 日本SGI株式会社
     * ヨハンソン・ヘンリク
     * johansson@sgi.co.jp
     * 
     * Change Log:
     * ---------------------------------------------------------------------------
     * 2009.01.07	Johansson Henrik		Class Created.
     * 2009.01.09							Updated QuerySegmentByKeyword to always return an XML string.
     *										Currently the application expect the reply to be an empty
     *										Xml Document even if the keyword is to long or no matching 
     *										segments were founds.
     * 2009.01.13							Changed the QuerySegmentByKeyword to take and pass on the
     *										users Db Id to the database. This is to enable logging of
     *										searches in the Db.
     */

    /// <summary>
    /// This static class provides methods for extracting, 
    /// updating and deleting Keywords in the Database.
    /// Use the static methods in this class to perform  
    /// Keyword related operations on the Database.
    /// </summary>
    /// <remarks>INSERT:Creds</remarks>
    internal static class KeywordManipulator
    {
        /// <summary>
        /// Original Name = QuerySegmentByKeyword
        /// </summary>
        /// <remarks>Done</remarks>
        /// <param name="userDbId">Users DB id. Used for operation Logging.</param>
        /// <param name="keywordToMatch">The Keyword to seach for</param>
        /// <param name="connection">Sql Connection</param>
        /// <returns>The Xml string that contains the response document.</returns>
        internal static string QuerySegmentByKeyword(int userDbId, string keywordToMatch, SqlConnection connection)
        {
            //Build The final result document
            XmlDocument doc = null;
            XmlElement dataRoot = XmlConstructor.FormatResponseDocument(ref doc);
            XmlElement resRoot = doc.CreateElement("segments");
            dataRoot.AppendChild(resRoot);

            string result = string.Empty;

            try
            {

                if (keywordToMatch.Length > 64)
                    throw new ArgumentException("Search string is too long. Max characters is 64.");

                List<ClipOperations.Segment> _matchingSegments =
                    new List<ClipOperations.Segment>();

                List<int> _segmentIdsToLoad = new List<int>();

                _segmentIdsToLoad =
                    ClipOperations.SearchOperations.SearchSegmentIdsByKeyword(
                     userDbId, keywordToMatch, connection);

                //Get each matching segment from the database.
                foreach (int relId in _segmentIdsToLoad)
                {
                    ClipOperations.Segment segment =
                        ClipOperations.SegmentLoader.LoadSegmentFromDatabase(relId, connection);

                    if (segment != null)
                        _matchingSegments.Add(segment);
                }

                foreach (ClipOperations.Segment segment in _matchingSegments)
                {
                    ClipOperations.Clip clip =
                        ClipOperations.ClipLoader.LoadClipFromDatabase(segment.ParentClipId, connection);

                    if (clip != null)
                    {
                        XmlElement clipRoot = XmlConstructor.AddClipNodeToDocument(doc, clip, resRoot);
                        XmlElement segmentRoot = XmlConstructor.AddSegmentNodeToClip(doc, segment, clip.OnAirDate, clipRoot);

                        XmlElement kwRoot = doc.CreateElement("keywords");
                        segmentRoot.AppendChild(kwRoot);

                        List<KeywordOperations.Keyword> segmentKeywords =
                            KeywordOperations.KeywordLoader.LoadKeywordsForSegmentFromDatabase(
                            segment.Id, connection);

                        if (segmentKeywords != null)
                        {
                            foreach (KeywordOperations.Keyword keyword in segmentKeywords)
                            {
                                XmlConstructor.AddKeywordNodeToSegment(
                                doc, keyword, kwRoot);
                            }
                        }
                    }
                }

                result = doc.OuterXml;

            }
            catch (Exception ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = doc.OuterXml;
            }
            return result;

        }//end:QuerySegmentByKeyword

        /// <summary>
        /// Get all keywords registered by the given User DB id.
        /// Original Name = QueryKeywords
        /// </summary>
        /// <remarks>Done</remarks>
        /// <param name="userDbId">User DB id to match</param>
        /// <param name="connection">Sql Connection</param>
        /// <returns>The Xml string that contains the response document.</returns>
        internal static string QueryKeywordsForUser(int userDbId, SqlConnection connection)
        {
            SqlCommand command = new SqlCommand();
            SqlDataReader reader = null;
            command.CommandTimeout = 60;

            XmlDocument doc = null;
            XmlElement dataRoot = XmlConstructor.FormatResponseDocument(ref doc);
            XmlElement dataNodeRoot = doc.CreateElement("keywords");
            dataRoot.AppendChild(dataNodeRoot);

            string result = string.Empty;
            try
            {
                command.Connection = connection;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "GetKeywordsByUserId";

                command.Parameters.Add("userId", SqlDbType.Int).Value = userDbId;

                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    if (doc != null)
                    {
                        while (reader.Read())
                        {
                            XmlElement kwNodeRoot = doc.CreateElement("keyword");
                            dataNodeRoot.AppendChild(kwNodeRoot);

                            int id = reader.GetInt32(0);
                            string word = reader.GetString(4);

                            XmlElement idE = doc.CreateElement("id");
                            idE.InnerText = id.ToString();
                            kwNodeRoot.AppendChild(idE);

                            XmlElement wordE = doc.CreateElement("keyword");
                            wordE.InnerText = word;
                            kwNodeRoot.AppendChild(wordE);
                        }
                    }
                }

                result = doc.OuterXml;
            }
            catch (NullReferenceException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = doc.OuterXml;
            }
            catch (SqlException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = doc.OuterXml;
            }
            catch (InvalidOperationException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = doc.OuterXml;
            }
            catch (IndexOutOfRangeException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = doc.OuterXml;
            }
            finally
            {
                command.Dispose();
                if (reader != null)
                {
                    reader.Close();
                    reader.Dispose();
                }
            }

            return result;
        }//end:QueryKeywordsForUser

        /// <summary>
        /// Add a keyword to a segment.
        /// Original Name = AddKeywordToSegment
        /// This method adds a segment keyword whose member_id is set to null.
        /// As of 01/09/09 the UserDbId is sent to the DB but it has no meaning.
        /// </summary>
        /// <remarks>Done</remarks>
        /// <param name="userDbId">User DB id</param>
        /// <param name="segmentId">Id of the segment the keyword belongs to</param>
        /// <param name="keyword">The keyword to add</param>
        /// <param name="connection">Sql Connection</param>
        /// <returns>True if the keyword was added</returns>
        internal static bool AddKeywordToSegment(int userDbId, int segmentId, string keyword, SqlConnection connection)
        {
            SqlCommand command = new SqlCommand();
            command.CommandTimeout = 60;

            bool result = false;
            try
            {
                command.Connection = connection;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "AddSegmentKeyword";

                command.Parameters.Add("userId", SqlDbType.Int).Value = userDbId;
                command.Parameters.Add("segmentId", SqlDbType.Int).Value = segmentId;
                command.Parameters.Add("keyword", SqlDbType.VarChar, 64).Value = keyword;

                SqlParameter returnValue = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
                returnValue.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(returnValue);

                command.ExecuteNonQuery();
                int resultCode = (int)returnValue.Value;

                if (resultCode == 0)
                {
                    result = true;
                }
            }
            catch (InvalidOperationException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = false;
            }
            catch (InvalidCastException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = false;
            }
            finally
            {
                command.Dispose();
            }

            return result;
        }//end:AddKeywordToSegment

        /// <summary>
        /// Delete a segment Keyword from the DB.
        /// Original Name = DeleteKeyword
        /// As of 01/09/09 the UserDbId is sent to the DB but it has no meaning.
        /// </summary>
        /// <remarks>Done</remarks>
        /// <param name="userDbId">User DB id</param>
        /// <param name="keywordId">Id of the keyword to delete</param>
        /// <param name="connection">Sql Connection</param>
        /// <returns>True if the keyword was deleted</returns>
        internal static bool DeleteKeywordById(int userDbId, int keywordId, SqlConnection connection)
        {
            SqlCommand command = new SqlCommand();
            command.CommandTimeout = 60;

            bool result = false;
            try
            {
                command.Connection = connection;
                command.CommandType = System.Data.CommandType.StoredProcedure;
                command.CommandText = "DeleteSegmentKeyword";

                command.Parameters.Add("userId", SqlDbType.Int).Value = userDbId;
                command.Parameters.Add("keywordId", SqlDbType.Int).Value = keywordId;

                SqlParameter returnValue = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
                returnValue.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(returnValue);

                command.ExecuteNonQuery();
                int resultCode = (int)returnValue.Value;

                if (resultCode == 0)
                {
                    result = true;
                }
            }
            catch (InvalidOperationException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = false;
            }
            catch (InvalidCastException ex)
            {
                ErrorHandler.LogMessage(ex.Message, false);
                result = false;
            }
            finally
            {
                command.Dispose();
            }
            return result;
        }//end:DeleteKeywordById
    }
}
